package cnki.kg.demo.util;

import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Component
public class SqlserverUtils {
    public List<HashMap<String, Object>> getMetaData(String sql, String connUrl, String userName, String passWord, String driverName) throws SQLException {
        List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
        HashMap<String, Object> map = new HashMap<String, Object>();
        Connection conn = null;
        ResultSet rs = null;
        ResultSet rscount = null;
        Statement pst = null;
        List<HashMap<String, Object>> hm = null;
        try {
            try {
                Class.forName(driverName);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            conn = DriverManager.getConnection(connUrl, userName, passWord);
            pst = conn.createStatement();
            rs = pst.executeQuery(sql);
            if (rs == null) return null;
            ResultSetMetaData md = rs.getMetaData(); // 得到结果集的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
            while (rs.next()) {
                map = new HashMap<String, Object>(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    String cloumnName = "";
                    String lable = md.getColumnLabel(i);
                    cloumnName = StringUtil.isBlank(lable) ? md.getColumnName(i) : lable;
                    // map.put(md.getColumnName(i), String.valueOf(rs.getObject(i)));
                    map.put(cloumnName, String.valueOf(rs.getObject(i)));
                }
                //if (!existMap(result, map)) // 过滤重复的记录
                result.add(map);
            }

        } catch (Exception ex) {
            throw ex;
        }
        return result;
    }
    public List<HashMap<String, Object>> executeSql(String sql, String connUrl, String userName, String passWord, String driverName) throws SQLException {
        List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
        HashMap<String, Object> map = new HashMap<String, Object>();
        Connection conn = null;
        ResultSet rs = null;
        ResultSet rscount = null;
        Statement pst = null;
        List<HashMap<String, Object>> hm = null;
        try {
            try {
                Class.forName(driverName);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            conn = DriverManager.getConnection(connUrl, userName, passWord);
            pst = conn.createStatement();
            rs = pst.executeQuery(sql);
            if (rs == null) return null;
            ResultSetMetaData md = rs.getMetaData(); // 得到结果集的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
            while (rs.next()) {
                map = new HashMap<String, Object>(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    String cloumnName = "";
                    String lable = md.getColumnLabel(i);
                    cloumnName = StringUtil.isBlank(lable) ? md.getColumnName(i) : lable;
                    // map.put(md.getColumnName(i), String.valueOf(rs.getObject(i)));
                    map.put(cloumnName, String.valueOf(rs.getObject(i)));
                }
                //if (!existMap(result, map)) // 过滤重复的记录
                result.add(map);
            }

        } catch (Exception ex) {
            throw ex;
        }
        return result;
    }
}
